package DAO;

import java.sql.*;
import javax.swing.*;
import connect.ConnectDB;
import java.util.Vector;
import javax.swing.table.DefaultTableModel;

public class DepartmentDAO {

    public Connection conn;
    ResultSet rsDept = null;
    Statement st;
    PreparedStatement pst;
    public ConnectDB cdb;

    public DepartmentDAO() {
        cdb = new ConnectDB();
        cdb.getConnect();
        this.conn = cdb.conn;

        loadDataDepartment();
    }

    public void loadDataDepartment() {
        try {
            String query = "SELECT "
                    + "             * "
                    + "     FROM "
                    + "             Departments";
            st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rsDept = st.executeQuery(query);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String login(String username, String password) {
        try {
            String query = "SELECT "
                    + "             * "
                    + "     FROM "
                    + "             Account "
                    + "     WHERE "
                    + "             USERNAME = ?";

            PreparedStatement pst = conn.prepareStatement(query);
            pst.setString(1, username);
            ResultSet rsAccount = pst.executeQuery();

            // Username is not valid
            if (!rsAccount.next()) {
                return "Username is not valid";
            }

            // Password is not valid
            if (!rsAccount.getString("PASSWORD").equals(password)) {
                return "Password is not valid";
            }

            // Login successfully
            // Check Access-Level
            if (rsAccount.getString("ACCESS").equals("U")) {
                return "User";
            }

            return "Admin";
        } catch (Exception e) {
            System.out.println("Error in login(): " + e.getMessage());
            return "Error in method login()";
        }

    }

    public DefaultTableModel loadTableDep() {
        try {
            rsDept.beforeFirst();

            // Load column name
            ResultSetMetaData rsmd = rsDept.getMetaData();

            int numCol = rsmd.getColumnCount();
            Vector colName = new Vector();

            colName.add(rsmd.getColumnName(1)); // Dept ID
            colName.add(rsmd.getColumnName(2)); // Dept Name

            DefaultTableModel dtm = new DefaultTableModel(colName, 0);

            // Load row data
            while (rsDept.next()) {
                Vector rowData = new Vector();
                rowData.add(rsDept.getString(1));
                rowData.add(rsDept.getString(2));
                dtm.addRow(rowData);
            }

            return dtm;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public boolean checkDupplicateDepID(int DepNo) {
        try {
            // Move cursor to Row of Column name
            rsDept.beforeFirst();

            // loop to find DepID
            while (rsDept.next()) {
                if (DepNo == rsDept.getInt("DEPTNO")) {  // DEPTNO is exist in database
                    return false;
                }
            }

            // DEPTNO not exist
            return true;
        } catch (Exception e) {
            System.out.println("Error: " + e.getMessage());
            return false;
        }
    }

    public DefaultTableModel loadEmpAsDep(int DepNo) {
        try {
            rsDept.beforeFirst();

            // Load Default table model
            String query = "SELECT "
                    + "             * "
                    + "     FROM "
                    + "             Employees "
                    + "     WHERE "
                    + "             DEPTNO = ?";
            PreparedStatement pst = conn.prepareStatement(query);
            pst.setInt(1, DepNo);
            ResultSet rs = pst.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();

            int numCol = rsmd.getColumnCount();
            Vector colName = new Vector();
            for (int i = 0; i < numCol; i++) {
                colName.add(rsmd.getColumnName(i + 1));
            }
            DefaultTableModel dtm = new DefaultTableModel(colName, 0);

            while (rs.next()) {
                Vector rowData = new Vector();
                for (int i = 0; i < numCol; i++) {
                    rowData.add(rs.getString(i + 1));
                }
                dtm.addRow(rowData);
            }

            rs.close();
            pst.close();
            return dtm;

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error: " + e.getMessage());
            return null;
        }
    }

    public DefaultTableModel loadSecAsDep(int DepNo) {
        try {
            rsDept.beforeFirst();

            // Load Default table model
            String query = "SELECT "
                    + "             * "
                    + "     FROM "
                    + "             Sections "
                    + "     WHERE "
                    + "             DEPTNO = ?";
            PreparedStatement pst = conn.prepareStatement(query);
            pst.setInt(1, DepNo);
            ResultSet rs = pst.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();

            int numCol = rsmd.getColumnCount();
            Vector colName = new Vector();
            for (int i = 0; i < numCol; i++) {
                colName.add(rsmd.getColumnName(i + 1));
            }
            DefaultTableModel dtm = new DefaultTableModel(colName, 0);

            while (rs.next()) {
                Vector rowData = new Vector();
                for (int i = 0; i < numCol; i++) {
                    rowData.add(rs.getString(i + 1));
                }
                dtm.addRow(rowData);
            }

            rs.close();
            pst.close();
            return dtm;

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error: " + e.getMessage());
            return null;
        }
    }

    public DefaultTableModel loadVacAsDep(int DepNo) {
        try {
            rsDept.beforeFirst();

            // Load Default table model
            String query = "SELECT "
                    + "             * "
                    + "     FROM "
                    + "             Vacancies "
                    + "     WHERE "
                    + "             DEPTNO = ?";
            PreparedStatement pst = conn.prepareStatement(query);
            pst.setInt(1, DepNo);
            ResultSet rs = pst.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();

            int numCol = rsmd.getColumnCount();
            Vector colName = new Vector();
            for (int i = 0; i < numCol; i++) {
                colName.add(rsmd.getColumnName(i + 1));
            }
            DefaultTableModel dtm = new DefaultTableModel(colName, 0);

            while (rs.next()) {
                Vector rowData = new Vector();
                for (int i = 0; i < numCol; i++) {
                    rowData.add(rs.getString(i + 1));
                }
                dtm.addRow(rowData);
            }

            rs.close();
            pst.close();
            return dtm;

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error: " + e.getMessage());
            return null;
        }
    }

    public boolean insertDep(int DepNo, String DepName, String DepHead, String DepLocation) {
        try {
            // move Cusor to new row (Insert row) in ResultSet
            rsDept.moveToInsertRow();

            // Insert value to ResultSet
            rsDept.updateInt(1, DepNo);
            rsDept.updateString(2, DepName);
            rsDept.updateString(3, DepHead);
            rsDept.updateString(4, DepLocation);

            // Commit
            rsDept.insertRow();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public Vector loadDepToUpdate(int DepNo) {
        // create vector to store Department data (1 row will be store)
        Vector vecDeptData = new Vector();
        try {
            // Move cursor to rist row (this is department data)
            rsDept.beforeFirst();

            // Loop to find the row which have this DepNo
            while (rsDept.next()) {
                if (DepNo == rsDept.getInt("DEPTNO")) {
                    // add all record form this row to vector to show
                    vecDeptData.add(rsDept.getObject(1));
                    vecDeptData.add(rsDept.getObject(2));
                    vecDeptData.add(rsDept.getObject(3));
                    vecDeptData.add(rsDept.getObject(4));
                }
            }

            return vecDeptData;
        } catch (Exception e) {
            System.out.println("Error in loadDepToUpdateForm: " + e.getMessage());
            return null;
        }
    }

    public boolean updateDep(int DepNo, String DepName, String DepHead, String DepLocation) {
        try {
            String query = "UPDATE "
                    + "             Departments "
                    + "     SET "
                    + "             DEPTNAME = ? ,"
                    + "             DEPTHEAD = ? ,"
                    + "             LOCATION = ? "
                    + "     WHERE "
                    + "             DEPTNO = ?";
            PreparedStatement pst = conn.prepareStatement(query);
            pst.setString(1, DepName);
            pst.setString(2, DepHead);
            pst.setString(3, DepLocation);
            pst.setInt(4, DepNo);

            if (pst.executeUpdate() == -1) {
                return false;
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean deleteDep(int DepNo) {
        try {
            rsDept.beforeFirst();
            while (rsDept.next()) {
                if (rsDept.getInt("DEPTNO") == DepNo) {
                    rsDept.deleteRow();
                    return true;
                }
            }
            return false;
        } catch (Exception e) {
            System.out.println("Have data in orther table");
            return false;
        }
    }

    public int getDepNoFormDepName(String DepName) {
        try {
            rsDept.beforeFirst();
            while (rsDept.next()) {
                if (DepName.equals(rsDept.getString("DEPTNAME"))) {
                    return rsDept.getInt("DEPTNO"); // <--- Take DepNo
                }
            }
            return 0;
        } catch (Exception e) {

            return 0;
        }
    }

    public String checkDepNo(int DepNo) {
        try {
            loadDataDepartment();
            while (rsDept.next()) {
                if (rsDept.getInt("DEPTNO") == DepNo) {
                    return "Exist";
                }
            }
            return "Not exist";
        } catch (Exception e) {
            e.printStackTrace();
            return "Error";
        }
    }

    public String getDepName(int DepNo) {
        try {
            rsDept.beforeFirst();
            while (rsDept.next()) {
                if (rsDept.getInt("DEPTNO") == DepNo) {
                    return "" + rsDept.getString("DEPTNAME");
                }
            }
            return "Not Found";
        } catch (Exception e) {
            e.printStackTrace();
            return "Error";
        }
    }

    public void closeConnect() {
        try {
            if (rsDept != null) {
                rsDept.close();
            }
            
            if (pst != null){
                pst.close();
            }
            
            if (st!= null) {
                st.close();
            }
            
            if (conn!=null){
                conn.close();
            }
            
            if (cdb.conn!=null) {
                cdb.conn.close();
            }
            System.out.println("Close connect: Department");
        } catch (Exception e) {
            System.out.println("Error in close connection: " + e.getMessage());
        }
    }
}
